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!
I have created a Dynamic Column Selection to use in a slicer (The slicer uses a dropdown with single select). When the user selects an option, I count all the results splitter by the values of that option.
I want to exclude blank values, for all that options, but when I try to do that via the visual, the results affect all other options from that slicer.
In the example below, follow these steps to reproduce:
1. Check how many rows exists on the right diagram, when the filter is Shirt Number
2. Change to Coffee Preference and try to exclude Blanks from the visual on the right
3. Return slicer to Shirt Number and check how the data are affected.
The issue is that when you exclude Blanks from the Coffee Preference, you miss the truth about other categories, in our case, Shirt Number, where the value "Large" does not exist.
Link to file: https://drive.google.com/file/d/1xevTPkEJiUiVj5z87P8CJBki5PFFoIcw/view?usp=drive_link
Thank you in advance!
Solved! Go to Solution.
Hi @TehKonnos ,
Please try below steps:
1. create a new table with below dax formula
Table =
VAR tmp1 =
SELECTCOLUMNS (
'Raw Data',
"Name", [Name],
"Property", "Coffee Preference",
"Value", [Coffee Preference]
)
VAR tmp2 =
SELECTCOLUMNS (
'Raw Data',
"Name", [Name],
"Property", "Occupied",
"Value", [Occupied]
)
VAR tmp3 =
SELECTCOLUMNS (
'Raw Data',
"Name", [Name],
"Property", "Shirt Number",
"Value", [Shirt Number]
)
VAR tmp4 =
UNION ( tmp1, tmp2, tmp3 )
VAR tmp5 =
FILTER ( tmp4, [Value] <> BLANK () )
RETURN
tmp5
2. create a measure with below dax formula
Measure = COUNT('Table'[Value])
3. visualize the data
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @TehKonnos ,
Please try below steps:
1. create a new table with below dax formula
Table =
VAR tmp1 =
SELECTCOLUMNS (
'Raw Data',
"Name", [Name],
"Property", "Coffee Preference",
"Value", [Coffee Preference]
)
VAR tmp2 =
SELECTCOLUMNS (
'Raw Data',
"Name", [Name],
"Property", "Occupied",
"Value", [Occupied]
)
VAR tmp3 =
SELECTCOLUMNS (
'Raw Data',
"Name", [Name],
"Property", "Shirt Number",
"Value", [Shirt Number]
)
VAR tmp4 =
UNION ( tmp1, tmp2, tmp3 )
VAR tmp5 =
FILTER ( tmp4, [Value] <> BLANK () )
RETURN
tmp5
2. create a measure with below dax formula
Measure = COUNT('Table'[Value])
3. visualize the data
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
102 | |
86 | |
77 | |
68 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |