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.
I have the original dataset as below
ID | DayID | Amount | Type |
1 | 1 | 12 | A |
1 | 1 | 40 | B |
1 | 3 | 10 | A |
1 | 3 | 20 | C |
2 | 3 | 30 | D |
2 | 4 | 40 | A |
2 | 3 | 50 | C |
3 | 4 | 60 | B |
3 | 4 | 70 | C |
I have created a slicer from the values of 'Type'.
I want to create another column in the table that times 'Amount' by 1 by default and 0 if the user selects it.
For example if the user selects 'A' from the slicer all the amount values in the new column that have type A should be multiplied by 0. the resulting table should look like
ID | DayID | Amount | Type | New Coulmn |
1 | 1 | 12 | A | 0 |
1 | 1 | 40 | B | 40 |
1 | 3 | 10 | A | 0 |
1 | 3 | 20 | C | 20 |
2 | 3 | 30 | D | 30 |
2 | 4 | 40 | A | 0 |
2 | 3 | 50 | C | 50 |
3 | 4 | 60 | B | 60 |
3 | 4 | 70 | C | 70 |
Can anyone please help with how I can do this?
Hi @Anonymous ,
You could create a calculated table.
Table 2 = DISTINCT('Table'[Type])
Then create a measure instead of a calculated column. Because a calculated column is static, while a masure is dynamic.
Measure = IF(ISFILTERED('Table 2'[Type])&&MAX('Table'[Type]) in ALLSELECTED('Table 2'[Type]),0*SUM('Table'[Amount]),SUM('Table'[Amount])*1)
When the slicer is not selected,
When 'A' is selected,
You can check more details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-stephen-msft Thanks for that it looks promissing.
I want to take this a step further and plot a histogram with Measure on the x-axis and count of ID on the y-axis.
How would I go about this as the histgram isn't letting me input Measure as values?
You cannot modify the calculated column value based on slicer selection as it is loaded once on the data load and not every time as user interacts with the filter.
You can always create a measure using the SELECTEDVALUE and use that in your visualisations based on your requirement.
New Column = var SlicerSelection = SELECTEDVALUE(Table[Type])
return
if SlicerSelection
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.