Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
lets say i have this sample data
ParamA | Step | paramC | Lot | value |
Test1 | A | conc | 1234 | 5 |
Test1 | B | conc | 1234 | 7 |
Test2 | A | Load | 1234 | 8 |
Test2 | B | conc | 1234 | 6 |
Test2 | B | Load | 1234 | 3 |
Test1 | A | conc | 5555 | 9 |
Test1 | B | conc | 5555 | 4 |
Test2 | A | Load | 5555 | 3 |
Test2 | B | conc | 5555 | 6 |
Test2 | B | Load | 5555 | 2 |
Test1 | A | conc | 7777 | 6 |
Test1 | B | conc | 7777 | 2 |
Test2 | A | Load | 7777 | 5 |
Test2 | B | conc | 7777 | 9 |
Test2 | B | Load | 7777 | 4 |
i want to create a group of filters that control one measure value sum
and another group of filters that control the second measure value sum
so i can compare them together in the same chart
on the i want the LOT
And on the y i want measure 1 that is controled by the first group of slicers
and on the secodary y i want measure 2 that is controled by the second group of slicers
every group has slicers for ParamA , Step and ParamC
i can do it with two diffrent visuals but i want it for comparsment in twho lines
here is an example of using two visuals for demonstration
any one have an idea how to solve with dax ?
Solved! Go to Solution.
Hi @ShaiG
Would duplicating your table be an option?
In Power Query I made a copy (FactTable and FactTable2). In addition, I created a Lot dimension table. (DimLot)
Add 1:many (single) relationships between DimLot and the fact tables.
It makes the measures very simple:
Val1 = SUM( 'FactTable'[value] )
Val2 = SUM( 'FactTable2'[value] )
It might be an option.
Measure Filtered by Group of slicers one for Y and the other for Y secondary.pbix
hi,
I think it might be worth to create set of disconnected tables. in your case it will be 6 tables for 2 groups of parameters. For that you can use either VALUES or DISTINT (if you want to drop blanks) function to create calculated tables which will serve as calculate modifiers.
Link to pbix:
https://drive.google.com/file/d/19iCfGyKotdnpFqFamX0ok5wEIy_mo8-Y/view?usp=sharing
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: www.linkedin.com/in/lukasz-kozdron
hey , thank you for your post
unfortnatly im looking for no duplicate tables solutions , there was already one solution that had 2 copy tables , but still im lookling for a no duplicate tables soluition .
thank you eitherway
Hi @ShaiG
Would duplicating your table be an option?
In Power Query I made a copy (FactTable and FactTable2). In addition, I created a Lot dimension table. (DimLot)
Add 1:many (single) relationships between DimLot and the fact tables.
It makes the measures very simple:
Val1 = SUM( 'FactTable'[value] )
Val2 = SUM( 'FactTable2'[value] )
It might be an option.
Measure Filtered by Group of slicers one for Y and the other for Y secondary.pbix
this is actually one possible option i know can work
but im trying to avoid to duplicate the all table cause its a big one and cause truble on filtering group A effecting Group B ( in this sample i used a small sample )
if you have a better option it can help
I'm not sure what you mean by "cause truble on filtering group A effecting Group B". With separate tables that wouldn't be an issue.
How many rows in your table?
60K , but need to use alot of slicers
still my client is using this kind of solution ( and its slow working )
i was hoping for a better one
maybe using AllExcept or something but it doesnt work , and i didnt understand why i cant use
two measures with diffrent slicers and show them together
try also simple AllExcept
in performance Analyzer it gives me this code that it doesnt show the ALExcept measure , but just the name of it
and its filtered by all sliceres so it gives me empy reasult
so i dont understand why it is
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({1234,
7777,
5555}, 'Sample'[Lot])
VAR __DS0FilterTable2 =
TREATAS({"B"}, 'Sample'[Step])
VAR __DS0FilterTable3 =
TREATAS({"Test1"}, 'Sample'[ParamA])
VAR __DS0FilterTable4 =
TREATAS({"conc"}, 'Sample'[paramC])
VAR __DS0FilterTable5 =
TREATAS({"Test2"}, 'Sample'[YparamA])
VAR __DS0FilterTable6 =
TREATAS({"A"}, 'Sample'[Ystep])
VAR __DS0FilterTable7 =
TREATAS({"Load"}, 'Sample'[YparamC])
VAR __DS0Core =
SUMMARIZECOLUMNS(
'Sample'[Lot],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
"ValueX", 'Sample'[ValueX],
"ValueY", 'Sample'[ValueY]
)
VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, [ValueX], 0, 'Sample'[Lot], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[ValueX] DESC, 'Sample'[Lot]
sorry, still not clear to me. I hope someone else can help you further.
not clear to me what you are trying to achieve. What are the measure definitions?
Hey ,
i'm trying to achive a comparesment between two measures in the same chart , with diffrent set of filters
the first measure is the sum of values with group A filters , that shows for every lot seleceted the value
the second measure is the sum of values with Group B filters , that show for every lot selected the value ( in a secondary line )
i started to try an idea i had to duplicate ParamA , Step , paramC and value to extra columns
and then create measures
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |