From list to distinct list and then on the basis of distinct list find count.

[Anonymous]

Hi,

I'm trying to count of names greater than 1 from ditisnct list.

Thanks in advance.

[v-shex-msft]

Hi @Anonymous,

I think you can write a measure with summarize and countrows functions to achieve your requirement.

Sample formula:

Result =
COUNTROWS (
    FILTER (
        SUMMARIZE ( 'Main List', [Name], "Count", COUNT ( 'Main List'[Name] ) ),
        [Count] > 1
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft @vanessafvg
Thanks for reply..

I'm getting result for only selected week only. But if I want a result from 0 - 41(selected filter).

*I'm  trying to find result for week 0-41. Suppose I select a week 41 in slicer , then I get main list (Image) and after that I trying to find distinct Count (Distinct list(image)) from week 0-41 and on top of that I trying to find count of names greater than 1.
All these in a measure1.

[v-shex-msft]

Hi @Anonymous,

Can you please share a pbix sample file with expected result? I will try it and share the formula if I can achieve these requirements.

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft

Thanks for helping me.

I Do't know how to attach sample file, so sharing link. Hope this will help.

https://drive.google.com/file/d/1_Hx45yeCaCs4-8TEpREvyvQ_YabCbZnV/view?usp=sharing

[v-shex-msft · Accepted solution]

HI @Anonymous,

If you want dynamic change 'distinct count' based slicer, you can try to use below formula.

Result = 
COUNTROWS (
    FILTER (
        SUMMARIZE ( ALLSELECTED(Table2), [Name], "Count", COUNT ( Table2[Name] ) ),
        [Count] > 1
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft

Thanks for helping me.

Actullly you selecting range, that's y you getting proper result but while drawing this to graph it will automatically select single week(as week is on X axis) and shows result for that particular week.

So when I draw graph, week 41 will show 33 unique customer greater than 1 . 

[vanessafvg]

@Anonymous you could do a group by in power query / m  with a count result and then creat a conditional column  where the count value is > 1?

[Anonymous]

Thanks, really appreciate you for response. But I've some doubts.

There are week , date col. on left and many other col..

What I'm trying to do.

 Suppose I select a week 41 in slicer , then I get main list (Image) and after that I trying to find distinct Count (Distinct list(image)) from week 0-41 and on top of that I trying to find count of names greater than 1...all these in a measure1.

thanks in advance

[Zubair_Muhammad]

@Anonymous

Try this MEASURE

Measure =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1[Name] ),
        CALCULATE ( COUNT ( Table1[Name] ) > 1 )
    )
)

[Anonymous]

Thanks Zubair,

I used the formula with little editing , but getting error. I trying to find result for week 0-41. Suppose I select a week 41 in slicer , then I get main list (Image) and after that I trying to find distinct Count (Distinct list(image)) from week 0-41 and on top of that I trying to find count of names greater than 1...all these in a measure1.

Measure =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1[Name] ),
        CALCULATE ( COUNT ( Table1[Name] ) > 1,table[week]<=max(table[week) )
    )
)

Thanks for help.. 

Cheers! 

[vanessafvg]

@Anonymous try this maybe?

Measure =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1[Name] ),
        CALCULATE (
            COUNT ( Table1[Name] ) > 1,
            FILTER ( table, table[week] <= MAX ( table[week] ) )
        )
    )
)