power bi loop

[BIswajit_Das]

Present data

id  response

1       a        

1       b        

1       a        

2       a        

3       b        

Required Data

id  response   Maxcountbasedata

1       a               a

1       b               a

1       a               a

2       a               a

3       b               b

[v-yiruan-msft · Accepted solution]

Hi @BIswajit_Das ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a calculated column as below to get the count of per response under per id

Count = 
CALCULATE (
    COUNT ( 'Table'[response] ),
    FILTER (
        'Table',
        'Table'[id] = EARLIER ( 'Table'[id] )
            && 'Table'[response] = EARLIER ( 'Table'[response] )
    )
)

2. Create a measure as below to get the response which have the max count

Maxcountbasedata = 
VAR _selid =
    SELECTEDVALUE ( 'Table'[id] )
VAR _count =
    CALCULATE (
        MAX ( 'Table'[Count] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[id] = _selid )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[response] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[id] = _selid
                && 'Table'[Count] = _count
        )
    )

Best Regards

[BIswajit_Das]

Actually i need the measure value in a column

[v-yiruan-msft · Accepted solution]

Hi @BIswajit_Das ,

You can create a calculated column as below to replace the measure, please find the details in the attachment.

Column =
VAR _count =
    CALCULATE (
        MAX ( 'Table'[Count] ),
        FILTER ( 'Table', 'Table'[id] = EARLIER ( 'Table'[id] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[response] ),
        FILTER (
            'Table',
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[Count] = _count
        )
    )

Best Regards

[NaimSiddiqui]

I liked this solution 

[BIswajit_Das]

I have this now

x  y  z

1  a  a

1  b  a

1  a  a

2  a  a

3  b  b

And i need to create a visual like

for a                                                            for b

2/3  (z-count/distintcount-x)                      1/3 

Is there any way to do this??

[v-yiruan-msft]

Hi @BIswajit_Das ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a z field dimension table

Z = VALUES('Table'[z])

2. Create a measure as below

Measure = 
VAR _selvalue =
    SELECTEDVALUE ( 'Z'[z] )
VAR _countx =
    DISTINCTCOUNT ( 'Table'[x] )
VAR _countz =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[x] ),
        FILTER ( 'Table', 'Table'[z] = _selvalue )
    )
RETURN
    DIVIDE ( _countz, _countx, 0 )

If you have new problem or requirement, you can post a new case in the community. Then more people will see it and resolve it shortly.

Best Regards

[BIswajit_Das]

actually my requirement is to set visuals like 2/3 and 0/3

I've posted this on new massage please check the below link

"https://community.powerbi.com/t5/Desktop/power-bi-visual/m-p/3109670/highlight/true#M1053860"

[v-yiruan-msft]

Hi @BIswajit_Das ,

I just reply you in the new case: power bi visual - Microsoft Power BI Community

Best Regards

[BIswajit_Das]

Can we help with this post

https://community.powerbi.com/t5/Desktop/Power-Bi-Table/m-p/3111393

Thanks you

[BIswajit_Das]

Thanks it's really helpfull

[v-yiruan-msft · Accepted solution]

Hi @BIswajit_Das ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a calculated column as below to get the count of per response under per id

Count = 
CALCULATE (
    COUNT ( 'Table'[response] ),
    FILTER (
        'Table',
        'Table'[id] = EARLIER ( 'Table'[id] )
            && 'Table'[response] = EARLIER ( 'Table'[response] )
    )
)

2. Create a measure as below to get the response which have the max count

Maxcountbasedata = 
VAR _selid =
    SELECTEDVALUE ( 'Table'[id] )
VAR _count =
    CALCULATE (
        MAX ( 'Table'[Count] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[id] = _selid )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[response] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[id] = _selid
                && 'Table'[Count] = _count
        )
    )

Best Regards

[BIswajit_Das]

Thanks a lot It's really helpful

But i need the measure value in a column to use it on the shape map

Is there any way to so