Conditional columns in SUMMARIZE...

[Matty]

Hi Team,

I have a use case at the moment where I need to create a new table (inside a measure) that considers a parameter set by the user.

I know I can do the following:

VAR Scenario A =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        Table1[Product],
        Table1[Week]
    )

VAR Scenario B =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        Table1[Category],
        Table1[Week]
    )

But what I'm wondering is whether it's possible to do something like this:

VAR Scenario =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        IF ( SELECTEDVALUE ( Table2[Selection] ) = "Product", Table1[Product], Table1[Category] )
        Table1[Week]
    )

Which isn't syntactically valid in its current form, but hopefully illustrates what I would like to be able to do, i.e. to decide what is or isn't included within SUMMARIZE.

Hoping for some guidance, even if it's just to confirm that this kind of approach isn't possible.

Cheers,

Matty

 

[Matty]

You need to build the model as I described earlier, then the formula I provided works.

https://www.dropbox.com/s/sz76w3tlhzvbenq/forecast.pbix?dl=1

Hi Matt,

I've had a look at your model, but it doesn't do what I need. Perhaps I haven't explained things clearly enough.

Here's what your model is showing:

It shows the same ABS Diff (437) in each table, which isn't what's required.

What's needed is, depending on the level of aggregation a user chooses, for the ABS Diff to be calculated at that level.

Here's what I mean based on the model I shared:

ABS Diff with aggregation at Category level = 273:

ABS Diff with aggregation at Product level = 363

For the calculation to work correctly, each row must be rolled up to the desired (chosen) level first, and before the abs diff is calculated.

It makes sense that the higher up you go aggregation-wise, the smaller the abs diff becomes, which is what you can see in the model I provided.

The reason for my original post was to identify whether there was a more efficient way to write the DAX I'd come up with, as the result it's returning is correct.

Cheers,

Matty

 

[Matt Allington]

I’m not ignoring you, but this will take some more thought. My first gut feel is to use field parameters to change the object in the visual. But I need to take a closer look, and I don’t have a lot of time atm.

 

[Matty]

Any further thoughts on this, Matt? Just interested to know about alternatives.

Cheers,

Matty

 

[Matt Allington]

Yeah, sorry. I've been busy. I need to concentrate and look in detail and I haven't found the right time to do this as yet. Let me see how the next few days go

 

[Matt Allington]

Please take a look now. This is the measure I wrote. I added a disconnected slicer to accept the user input.

ABS Diff =
VAR Choice =
    SELECTEDVALUE ( Options[Choice] )
VAR CatChoice =
    SUMX (
        SUMMARIZE ( data, 'Product'[Category], Data[Week] ),
        CALCULATE ( ABS ( SUM ( Data[Forecast] ) - SUM ( Data[Orders] ) ) )
    )
VAR ProdChoice =
    SUMX (
        SUMMARIZE ( data, 'Product'[Product], Data[Week] ),
        CALCULATE ( ABS ( SUM ( Data[Forecast] ) - SUM ( Data[Orders] ) ) )
    )
RETURN
    SWITCH ( Choice, "Category", CatChoice, "Product", ProdChoice )

https://www.dropbox.com/s/sz76w3tlhzvbenq/forecast.pbix?dl=1