Forum Discussion
Dax dynamic grouping based on the parameter passed by users
Hi all,
I am trying to migrate tableau report to power bi report.
The table looks like this:
Category IP Diff Date
Lay A 5 4/22/2018
Lay A 10 4/21/2018
Lay B 10 4/21/2018
Sch C 40 4/20/2018
Sch B 20 4/21/2018
Sch C 30 4/21/2018
Rtl A 20 4/21/2018
In tableau we defined a calculated field called "IP Diff Count" using fixed:
{FIXED [IP] : SUM([Diff])}
In our scenario, we have one low parameter and high parameter which allows users to pass the offset value of IP Diff Count and then we created this measure:
IF ATTR([Category]) == "lay" THEN
IF ATTR([IP Diff Count]) >= [High] THEN
"High (>=[High])"
ELSEIF ATTR([IP Diff Count]) > [Low] AND ATTR([IP Diff Count]) < [High] THEN
"Medium ([Low]<>[High])"
ELSEIF ATTR([IP Diff Count]) > 0 AND ATTR([IP Diff Count]) <= [Low] THEN
"Low (<=[Low])"
ELSE
NULL
END
ELSEIF.....(for other two categories)Baiscially it means if the measure IP Diff Count for that category is smaller then the parameter pass by the user it will count it as "Low" otherwise it will be "Medium" or "High"
The final visual on tableau report is to display CNTD(IP) (distinct count of IP for "Low", "Medium" and "High")
(I already create a calculated column "IP DIFF COUNT" Using)
IP DIFF COUNT = CALCULATE(SUM(Table1[Diff]),FILTER(Table1,Table1[IP]=EARLIER(Table1[IP])))
For example based on the table above, and let's say if the user pass parameter for low "25" and high "35" It should return something like
Date
Category 4/20/2018 4/21/2018 4/22/2018
Low Medium High Low Medium High Low Medium High
"rtl"
"Sch"
"Lay"
THX
Hi
I use your dataset and measures (modified to fit in Power BI), finally, I get this
Does this meet your needs?
My steps are as follow
1.Create new parameters in Editor Query, after they are created, right-click on the parameter and make the “Enable Load” checked, so the parameter can be load to the data model and used in the measure.
2.Create two measures:
IP DIFF COUNT = CALCULATE ( SUM ( Table1[Diff] ), FILTER ( Table1, Table1[IP] = SELECTEDVALUE ( Table1[IP] ) ) )Measure2 = IF ( MAX ( [Category] ) = "Lay", IF ( [IP DIFF COUNT] >= MAX ( High[High] ), "High", IF ( [IP DIFF COUNT] > MAX ( Low[Low] ), "Medium", IF ( [IP DIFF COUNT] > 0 && [IP DIFF COUNT] <= MAX ( Low[Low] ), "Low", BLANK () ) ) ), IF ( MAX ( [Category] ) = "Sch", IF ( [IP DIFF COUNT] >= MAX ( High[High] ), "High", IF ( [IP DIFF COUNT] > MAX ( Low[Low] ), "Medium", IF ( [IP DIFF COUNT] > 0 && [IP DIFF COUNT] <= MAX ( Low[Low] ), "Low", BLANK () ) ) ), IF ( MAX ( [Category] ) = "Rtl", IF ( [IP DIFF COUNT] >= MAX ( High[High] ), "High", IF ( [IP DIFF COUNT] > MAX ( Low[Low] ), "Medium", IF ( [IP DIFF COUNT] > 0 && [IP DIFF COUNT] <= MAX ( Low[Low] ), "Low", BLANK () ) ) ) ) ) )3.add columns to a matrix
Best Regards
Maggie
2 Replies
- v-juanli-msftCommunity Support
Hi
I use your dataset and measures (modified to fit in Power BI), finally, I get this
Does this meet your needs?
My steps are as follow
1.Create new parameters in Editor Query, after they are created, right-click on the parameter and make the “Enable Load” checked, so the parameter can be load to the data model and used in the measure.
2.Create two measures:
IP DIFF COUNT = CALCULATE ( SUM ( Table1[Diff] ), FILTER ( Table1, Table1[IP] = SELECTEDVALUE ( Table1[IP] ) ) )Measure2 = IF ( MAX ( [Category] ) = "Lay", IF ( [IP DIFF COUNT] >= MAX ( High[High] ), "High", IF ( [IP DIFF COUNT] > MAX ( Low[Low] ), "Medium", IF ( [IP DIFF COUNT] > 0 && [IP DIFF COUNT] <= MAX ( Low[Low] ), "Low", BLANK () ) ) ), IF ( MAX ( [Category] ) = "Sch", IF ( [IP DIFF COUNT] >= MAX ( High[High] ), "High", IF ( [IP DIFF COUNT] > MAX ( Low[Low] ), "Medium", IF ( [IP DIFF COUNT] > 0 && [IP DIFF COUNT] <= MAX ( Low[Low] ), "Low", BLANK () ) ) ), IF ( MAX ( [Category] ) = "Rtl", IF ( [IP DIFF COUNT] >= MAX ( High[High] ), "High", IF ( [IP DIFF COUNT] > MAX ( Low[Low] ), "Medium", IF ( [IP DIFF COUNT] > 0 && [IP DIFF COUNT] <= MAX ( Low[Low] ), "Low", BLANK () ) ) ) ) ) )3.add columns to a matrix
Best Regards
Maggie
- rawmeatFrequent Visitor
Thanks Maggie.
Sorry for the incomplete information I gave.
These are the expressions I write:
I first use ealier to get IP DIFF COUNT.
Then
Friday sch(High) = CALCULATE(DISTINCTCOUNT(Friday[IP]),FILTER(Friday,Friday[IP DIFF COUNT]>=[HighSch Value] && Friday[CATEGORY]="sch")) Friday sch(low) = CALCULATE(DISTINCTCOUNT(Friday[IP]),FILTER(Friday,Friday[IP DIFF COUNT]>0&&Friday[IP DIFF COUNT]<=[LowSch Value] && Friday[CATEGORY]="sch")) Friday sch(Medium) = CALCULATE(DISTINCTCOUNT(Friday[IP]),FILTER(Friday,Friday[IP DIFF COUNT]>[LowSch Value] && Friday[IP DIFF COUNT]<[HighSch Value] && Friday[CATEGORY]="sch"))
So instead of using one expression I created three measures here.
Thanks again