Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |