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 Power BI community,
Can I have 2 Switch True measures to affect 1 same measure??
I am using Switch True in my DAX to show my Gross Sales, Net Sales and Volume for our customers just like the video link below.
Switch True demo by Enterprise DNA
And I have this measure as a slicer in my BI file.
Hence, if I select Gross Sales in my slicer, all the visualization will show Gross Sales.
If I select Volume, they will all show volume data.
Now, my question is,
will it be possible to have another Switch True slicers to affect the same measure?
Specifically, I would like to have a customer analysis on their growth/decline over the years
and I am using the slicer to see Gross Sales, Net Sales and Volume growth/decline by customers.
In this, I would like to add ranking as a slicer.
I am currently using a ranking measure to rank all my customers.
It would be great if I could another slicer on my page to filter the page with below 5 segments.
That way, I can understand quickly if my top 20 customers are growing or declining and I can do the same analysis for my top 50 or 100 customers.
Thank you so much for your support!
H
Hi @hidenseek9 ,
I have another solution, hope that's suitable for you.
Assuming that you have a slicer column for Top 20 / 50 / 100 / 200 and All customers.
And you need to create a measure in this table for the top x, like:
top x slicer = SWITCH(MAX('Table'[Column1]),
"TOP 20",20,
"TOP 50",50,
"TOP 100",100,
"TOP 200",200,
9999,
)
Then please change your 3 measures(Gross Sales, Net Sales, and Volume) to below type measure:
New Gross Sales = IF(RANKX(ALL(your_customer_id),Your_original_gross_sales_measure) <= [top x slicer],Your_original_gross_sales_measure,BLANK())
It works on my desktop like below screenshot, please try.
Aiolos Zhao
Proud to be a Super User!
This did not work for me unfortunately.
But it's okay.
I found a different solution to my problem.
Thanks,
H
Hi @hidenseek9
Happy to know you've fixed, would you mind kindly sharing your solution here to benefit others? thanks a lot!
I found a work around to solve my specific problem.
I just modified my original data to fit my needs.
Therefore, I did not come up with a DAX formula and such to solve the problem so I doubt this helps anyone.
Thanks,
H
HI @hidenseek9 ,
I am assuming you are using a disconnected/disassociated table to dynamically switch between measures. What you are trying to achive should be possible. Your SWITCH measure would be something like this:
Measure =
VAR SV1 =
SELECTEDVALUE ( 'DisconnectedTable1'[Column] )
VAR SV2 =
SELECTEDVALUE ( 'DisconnectedTable2'[Column] )
RETURN
SWITCH (
TRUE (),
SV1 = "Criteria1"
&& SV2 = "Criteria2", [Measure1],
SV1 = "Criteria2"
&& SV2 = "Criteria2", [Measure2]
//additional criteria and result...
)
Proud to be a Super User!
Thank you for such a quick reply.
Does this mean I would need to create measures for each scenario?
Thanks,
H
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |