DAX - Measure that returns specific columns based on filter selection

[Anonymous]

Hello all,

In my Fact table (Coming from SQL Server) I have two columns, [ProductsSold], and [ProdSoldPerDay]. I want my users to have the ability to filter visuals with a slicer.

Let's say they have a table, right now both columns are in there, and then I have [Margin (Total)] and [Margin (Per Day]. That's a total of 4 columns. I want for the table (or any other visual) to only have 2 fields, in this scenario: [Product] and [Margin], in which both values change depending on the which value is selected by the slicer.

The slicer will have two selection values: Tota lProducts and Products per Day. "Total Products" will make the table show the values in [ProductsSold] and [Margin (Total)], while "Products Per Day" will show values from [ProdSoldPerDay] and [Margin (Per Day)].

I got the idea from @EnterpriseDNA's post in the gallery (here's his tutorial: https://www.youtube.com/watch?v=jXkGbNDAslo&feature=emb_title), but his example was a lot more complex and I can't figure out how to simplify it. 

I apologize in advance, my DAX is really weak because my company's IT group prefers we do as much as we can in SQL, but this feature would require me to program in DAX.

Thanks everyone! 

@Anonymous 

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data as follows.

Table:

Parameter:

You may create measures as follows.

SumMarginPerDay = CALCULATE(
                    SUM('Table'[Margin Per Day]),
                    ALLEXCEPT('Table','Table'[ID])
)

SumMarginTotal = CALCULATE(
                       SUM('Table'[Margin Total]),
                       ALLEXCEPT('Table','Table'[ID])
)

SumProductPerDay = CALCULATE(
                            SUM('Table'[ProductSoldPerDay]),
                            ALLEXCEPT('Table','Table'[ID])
)

SumPtoductTotal = CALCULATE(
                     SUM('Table'[ProductSold]),
                     ALLEXCEPT('Table','Table'[ID])
)

Margin = 
SWITCH(
       TRUE(),
       SELECTEDVALUE(Parameter[Metric])="Total Products",[SumMarginTotal],
       SELECTEDVALUE(Parameter[Metric])="Products Per Day",[SumMarginPerDay],
       0
) 

Product = 
SWITCH(
       TRUE(),
       SELECTEDVALUE(Parameter[Metric])="Total Products",[SumPtoductTotal],
       SELECTEDVALUE(Parameter[Metric])="Products Per Day",[SumProductPerDay],
       0
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SivaMani · Accepted solution]

Hi @Anonymous,

You can create a calculated measure like below,

Value = IF(SELECTEDVALUE(Table Name[Column name]) = "Products per Day", SUM(ProdSoldPerDay),SUM(ProductsSold))

Use this measure in Visuals. By default, it will show SUM(ProductsSold).

Hope it will help you!