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
Hello,
I would like to do this command in DAX and not in M (too many datas)
My table : a list of number from 0
Before :
1
PieceRefNumber
0
514
514
515
515
714
714
714
1020
The result I want is :
1 2 3 4
Groupby_PieceRefNumber Index_PieceRef Nb Occurence Difference
condition => filter no 0 condition : start with (1-2)
1st number in Groupby_
514 514 2 0
515 515 2 0
714 516 3 198
1020 517 1 503
Is it possible to do it in DAX : the function GROUP BY and INDEX creation ?
and if so, with calculated columns or measures please ?
Thank you so much for your help !
Solved! Go to Solution.
Hi @chanal ,
Use this measure
Index_PieceRef def =
MINX (
FILTER (
ALL ( 'Table 1' ),
'Table 1'[PieceRefNumber] > 0
),
'Table 1'[PieceRefNumber]
)
+
RANKX (
FILTER (
ALL ( 'Table 1'),
'Table 1'[PieceRefNumber] > 0
),
CALCULATE (
MAX ( 'Table 1'[PieceRefNumber] )
),
,
ASC,
DENSE
) - 1
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@chanal , Create a table like
Table1 = summarize(Table,Table[PieceRefNumber], "Nb Occurence",Count(Table[PieceRefNumber]))
and add these new columns
Index_PieceRef = min(Table1[PieceRefNumber]) + rank(all(Table1), [PieceRefNumber],,asc,dense)
diff = [PieceRefNumber]- [Index_PieceRef]
You can combine all these three using add columns
https://docs.microsoft.com/en-us/dax/addcolumns-function-dax
Hello @amitchandak
Thank you for your answer.
It has worked well with the formula Summarize.
But I have a problem with the formula Rank
PowerBI does not accept Rank only, but Rank.eq or Rankx
so I took the Rank.eq, and gives me this :
Table 1
PieceRef Number Nb Occurence Index_PieceRef
0 1 5
514 2 4
515 2 3
714 3 2
1020 1 1
so, there are 2 problems :
1- The order is descending instead af ascending
2- It takes the number 0 into account, and i don't want that it begins with 0, but with 514
if I filter in the column "PieceRefNumber" => no 0, I have this :
Table 1
PieceRef Number Nb Occurence Index_PieceRef
514 2 4
515 2 3
714 3 2
1020 1 1
This is better, but it is not in the order acending and it begins with 1 and not 514.
I give you my example pbix file : https://drive.google.com/file/d/17fYuw_70Xb1B8sw1mdJzS1okbIl4ooK3/view?usp=sharing
Thank you so much 🙂
Hi @chanal ,
You need to provide Access.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @chanal ,
You can try
Create Measures
Index_PieceRef =
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[PieceRefNumber] > 0
),
'Table'[PieceRefNumber]
)
+ RANKX (
FILTER (
ALL ( 'Table'[PieceRefNumber] ),
'Table'[PieceRefNumber] > 0
),
CALCULATE (
MAX ( 'Table'[PieceRefNumber] )
),
,
ASC,
DENSE
) - 1
Differnce = MAX('Table'[PieceRefNumber]) - [Index_PieceRef]
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hello @harsh
Thank you for your answer.
I tried with your formulas and it is nearly there, because i don't have the same results as you :
the index begins with 515 and finish with 515
I researched why and i dont' see => can you look at the model please ? (The link access is now public)
https://drive.google.com/file/d/1S16sD-BCT9uBkKLyiuEueJCEEUSd38R-/view?usp=sharing
Thank you very much
Kind regards
Hi @chanal ,
Use this measure
Index_PieceRef def =
MINX (
FILTER (
ALL ( 'Table 1' ),
'Table 1'[PieceRefNumber] > 0
),
'Table 1'[PieceRefNumber]
)
+
RANKX (
FILTER (
ALL ( 'Table 1'),
'Table 1'[PieceRefNumber] > 0
),
CALCULATE (
MAX ( 'Table 1'[PieceRefNumber] )
),
,
ASC,
DENSE
) - 1
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Yes, it works ! thank you so much for your help !
i give the link with the successful example file : https://drive.google.com/file/d/1jfjgozpaCrIFsCeejF2VfuQrxW1HUtUi/view?usp=sharing
A real Champion 🙂
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 |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |