Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all, I am building a funnel chart. I am trying to have an overall/generic showing when there is no slicer selection and poduct specific categorys when there is a selection.
Here is my DAX:
Solved! Go to Solution.
Hi @Clikojo ,
It looks like the issue is with the way the SWITCH function is being used. The error occurs because SWITCH is trying to compare a text value with a boolean value. To fix this, you can use the SWITCH(TRUE(), ...) pattern, which allows for more flexible comparisons.
Here’s how you can modify your DAX formula:
DynamicStateGroup =
IF (
ISFILTERED('ProductTable'[Product]), -- Check if something is selected in the slicer
'stg_product_onboarding'[updated_state], -- If something is selected, return the original state
SWITCH(
TRUE(),
'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state]
)
)
Give this a try and let me know if it works for your funnel chart! If yes, please consider to accept as solution and give a Kudo.
You can lear more about SWITCH function here
Thank you.
Hi @Clikojo ,
It looks like the issue is with the way the SWITCH function is being used. The error occurs because SWITCH is trying to compare a text value with a boolean value. To fix this, you can use the SWITCH(TRUE(), ...) pattern, which allows for more flexible comparisons.
Here’s how you can modify your DAX formula:
DynamicStateGroup =
IF (
ISFILTERED('ProductTable'[Product]), -- Check if something is selected in the slicer
'stg_product_onboarding'[updated_state], -- If something is selected, return the original state
SWITCH(
TRUE(),
'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state]
)
)
Give this a try and let me know if it works for your funnel chart! If yes, please consider to accept as solution and give a Kudo.
You can lear more about SWITCH function here
Thank you.
@Bibiano_Geraldo Thank you for your help. The issue I am still having is that the count changes when a selection is made but the funnel visual does not. With no slicer selection it shows the grouped categorys and with a selection the same thing but just with the correct count. I am entering this DAX as a new column within my table. This is the correct approach right?
Hi @Clikojo ,
To achieve dynamic behavior based on slicer selection, you should use a measure instead of a calculated column. Measures recalculate dynamically based on the filters and slicers applied in your report.
Here's revised DAX measure:
DynamicStateGroupMeasure =
IF (
ISFILTERED('ProductTable'[Product]), -- Check if something is selected in the slicer
MAX('stg_product_onboarding'[updated_state]), -- If something is selected, return the original state
SWITCH(
TRUE(),
MAX('stg_product_onboarding'[updated_state]) = "Onboarding Success", "Onboarding Success",
MAX('stg_product_onboarding'[updated_state]) IN {"contract_1", "contract_2"}, "Sign Contracts",
MAX('stg_product_onboarding'[updated_state])
)
)
Now you can use this measure in the funnel chart as your dynamic state field.
I hope this helps! 🎯
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
👍 Give it a kudo to show your appreciation!
@Clikojo You need to use the SWITCH( TRUE(), ... ) version like this:
IF (
ISFILTERED('ProductTable'[Product]), -- Check if something is selected in the slicer
'stg_product_onboarding'[updated_state], -- If something is selected, return the original state
SWITCH( TRUE(),
'stg_product_onboarding'[updated_state] IN {"contract_1, "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state]
)
)
Thanks @Greg_Deckler, that seemed to resolve my error however, my funnel chart is not behaving as expected. These grouped categorys remain regardless if I have a selection on my slicer or not. Are you able to give some pointers as this might be happening please?
@Clikojo are there different tables you have used to built the funnel data, if so than it might be the relationship problem.
@SenPower I am using the same table 'stg_product_onboarding' for both the slicer and funnel. I use producttype for the slicer and trying to use dynamicstategroup in category for funnel visual
@Clikojo Can you modify your measure with this
DynamicStateGroup =
IF (
HASONEVALUE('ProductTable'[Product]),
VALUES('stg_product_onboarding'[updated_state]),
SWITCH(
TRUE(),
'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state]
)
)
@SenPower Thank you for your help. The issue I am still having is that the count changes when a selection is made but the funnel visual does not. With no slicer selection it shows the grouped categorys and with a selection the same thing but just with the correct count. I am entering this DAX as a new column within my table. This is the correct approach right?
Ahhh, i undersrtand now, it seems like you have created a calculated column rather than a measure. Please create a measure not the column using the below DAX and is the table you are using as STG_Product_Onboarding check and see if that table depends on other tables if so than check the relationships of the table.
But test it with the below DAX as a mesure not the calculated column.
DynamicStateGroup =
IF (
ISFILTERED('ProductTable'[Product]),
SELECTEDVALUE('stg_product_onboarding'[updated_state]),
SWITCH(
TRUE(),
'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state]
)
)
User | Count |
---|---|
73 | |
70 | |
38 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
43 | |
42 |