Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Pillic
Helper II
Helper II

Find distinct numbers in group

‎09-25-2024 09:18 AM

Hello community,

 

I have a table like this (the bold text shows just the group size = 3 rows):

DatePRUEFNUMBERMATNRfci
01.07.20249E+10201290611008956340,9
01.07.20249E+10202126421010142537
01.07.20249E+10201369501008962546,4
02.07.20249E+10201369711008948752
02.07.20249E+10201290611008956346,1
02.07.20249E+10201369711008948731,7
03.07.20249E+10201369721008965555
05.07.20249E+10200862811009139146,4
04.07.20249E+10200861851008921223,5
04.07.20249E+10201290611008956350,5
04.07.20249E+10201290611008956344,8
04.07.20249E+10201290611008956356,4
05.07.20249E+10202019621009643353,3

 

And added here the desired columns

 

lfd NumDatePRUEFNUMBERMATNRfci3xAVGDistinct countAVG same productsnew 3xAVG
101.07.20249E+10201290611008956340,9 1  
201.07.20249E+1020212642101014253741,431 41,43
301.07.20249E+10201369501008962546,4 1  
402.07.20249E+10201369711008948752 241,85 
502.07.20249E+10201290611008956346,143,271 43,98
602.07.20249E+10201369711008948731,7 241,85 
703.07.20249E+10201369721008965555 1  
805.07.20249E+10200862811009139146,441,631 41,63
904.07.20249E+10200861851008921223,5 1  
1004.07.20249E+10201290611008956350,5 3  
1104.07.20249E+10201290611008956344,850,57350,5750,57
1204.07.20249E+10201290611008956356,4 3  
1305.07.20249E+10202019621009643353,3 1  

 

I was able to add some of the required columns with visual calulations, unfortunately it is not possible then to export the view to csv/excel.

 

 

 

 

lfd Num = FORMAT(RANK(DENSE,ROWS), "#")
3xAVG = 
VAR _rang = FORMAT(RANK(DENSE,ROWS), "#")
VAR GroupSize = 3

RETURN
IF(MOD(_rang, GroupSize) = 2,
   DIVIDE(( [fci] + NEXT([fci]) + PREVIOUS([fci])),3),

 

 

 

 

 But now I am stuck at the counting the distinct MATNR in each group and the following columns.

It would be great if you could advise the measures to get rid of the visual calculations.

 

Thank you

Labels:
Message 1 of 9
235 Views
0
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support
In response to Pillic

‎09-30-2024 12:00 AM

Hi @Pillic 

I change the measures to the following,you can refer to.

Mod = MOD([RankingIndex],3)
Group =
VAR a = [RankingIndex]
VAR b =
    COUNTROWS (
        FILTER ( ALLSELECTED ( 'Table' ), [RankingIndex] < a && [Mod] = 0 )
    )
RETURN
    IF ( b > 0, b, 0 )

Distinctcount =
VAR a = [Group]
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Group] = a
                && [MATNR] = MAX ( 'Table'[MATNR] )
        )
    )

 

AVG same products =
VAR a = [Group]
VAR b =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Group] = a
                && [MATNR] = MAX ( 'Table'[MATNR] )
        ),
        [fci]
    )
RETURN
    IF ( [Distinctcount] > 1, DIVIDE ( b, [Distinctcount] ) )

Then it can work.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Find distinct numbers in group.pbix
Message 6 of 9
133 Views
0
8 REPLIES 8
v-xinruzhu-msft
Community Support
Community Support

‎09-25-2024 07:16 PM

Hi @Pillic 

Based on your description, you can refer to the following solution.

 I create an index column in table.

vxinruzhumsft_0-1727316665622.png

Then create the following measures.

 

Mod = MOD(MAX('Table'[Index]),3)
Group =
VAR a =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Index] < MAX ( 'Table'[Index] )
                && [Mod] = 0
        )
    )
RETURN
    IF ( a > 0, a, 0 )

Distinctcount =
VAR a = [Group]
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Group] = a
                && [MATNR] = MAX ( 'Table'[MATNR] )
        )
    )

AVG same products =
VAR a = [Group]
VAR b =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Group] = a
                && [MATNR] = MAX ( 'Table'[MATNR] )
        ),
        [fci]
    )
RETURN
    IF ( [Distinctcount] > 1, DIVIDE ( b, [Distinctcount] ) )

 

Then create a table visual , put the distinctcount measure and the AVG same products measure to the  visual.

vxinruzhumsft_1-1727316852941.png

 

Output

vxinruzhumsft_2-1727316865029.png

 

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Find distinct numbers in group.pbix
Message 2 of 9
200 Views
0
Pillic
Helper II
Helper II
In response to v-xinruzhu-msft

‎09-29-2024 04:15 AM

@v-xinruzhu-msft 
Hi and thank you, Yolo!

your solutions look very promising, although I am struggeling with the 1st step, the ranking (without the visual calculation).

 

Pillic_0-1727608265596.png

In the table I do have an Index column, but that will become noncontinious after some filtering.
How did you created the ranking?

Message 3 of 9
175 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Pillic

‎09-29-2024 06:16 PM

Hi @Pillic 

Thanks for your quick reply, based in your descriotion, you can change yor ranking index measure to the following.

Measure =
RANKX (
    ALLSELECTED ( LieferscheineUnique ),
    CALCULATE ( SUM ( LieferscheineUnique[Index] ) ),
    ,
    asc,
    DENSE
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Message 4 of 9
148 Views
0
Pillic
Helper II
Helper II
In response to v-xinruzhu-msft

‎09-29-2024 11:29 PM

Hi @v-xinruzhu-msft ,

 

thanks to your measure, I was able to create the rank like so:

 

RankingIndex = 
IF(HASONEVALUE(LieferscheineUnique[fci_zu_PA]), 
    RANKX (
        ALLSELECTED ( LieferscheineUnique ),
            CALCULATE ( SUM ( LieferscheineUnique[Index] ) ),
    ,
    asc,
    DENSE
), BLANK())

 

 

But now the Group measure shows this:

 

Group = 
VAR a =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( LieferscheineUnique ),
            [RankingIndex] < MAX([RankingIndex])
                && [Mod] = 0
        )
    )
RETURN
    IF ( a > 0, a, 0 )

 


Error: 

The 'MAX' function only accepts a column reference as an argument.

It would be great, if you could help me out to fix this.

Thanks a lot for your help so far.

Message 5 of 9
141 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Pillic

‎09-30-2024 12:00 AM

Hi @Pillic 

I change the measures to the following,you can refer to.

Mod = MOD([RankingIndex],3)
Group =
VAR a = [RankingIndex]
VAR b =
    COUNTROWS (
        FILTER ( ALLSELECTED ( 'Table' ), [RankingIndex] < a && [Mod] = 0 )
    )
RETURN
    IF ( b > 0, b, 0 )

Distinctcount =
VAR a = [Group]
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Group] = a
                && [MATNR] = MAX ( 'Table'[MATNR] )
        )
    )

 

AVG same products =
VAR a = [Group]
VAR b =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Group] = a
                && [MATNR] = MAX ( 'Table'[MATNR] )
        ),
        [fci]
    )
RETURN
    IF ( [Distinctcount] > 1, DIVIDE ( b, [Distinctcount] ) )

Then it can work.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Find distinct numbers in group.pbix
Message 6 of 9
134 Views
0
Pillic
Helper II
Helper II
In response to v-xinruzhu-msft

‎09-30-2024 01:01 AM

@v-xinruzhu-msft 

Thanks a lot...that works like a charm now (although the performance while slicing is reduced, but I can live with that)


May I kindly ask, to also help with the last required column. Which equals basicly the

DIVIDE ( Average (AVG same products) + fci_zu_PA (where __Distinctcount = 1) , 2)
and all within each group.

Pillic_1-1727683186297.png

 

Best Regards.

Message 7 of 9
128 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Pillic

‎09-30-2024 01:33 AM

Hi @Pillic 
You can try the following.

MEASURE =
VAR a = [Group]
VAR b =
    MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Group] = a ), [AVG same products] )
VAR c =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Group] = a && [Distinctcount] = 1 ),
        [fci]
    )
RETURN
    IF ( [Mod] = 2 && b > 0 && c > 0, DIVIDE ( b + c, 2 ), [3xAVG] )

Output

vxinruzhumsft_0-1727685221882.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Find distinct numbers in group.pbix
Message 8 of 9
121 Views
0
Pillic
Helper II
Helper II
In response to v-xinruzhu-msft

‎09-30-2024 01:44 AM

@v-xinruzhu-msft 

Thank you so much for your help! your proposal works as acpected.

Message 9 of 9
116 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All