Forum Discussion

BIswajit_Das's avatar
BIswajit_Das
Impactful Individual
3 years ago
Solved

power bi loop

Present data id  response 1       a         1       b         1       a         2       a         3       b         Required Data id  response   Maxcountbasedata 1       a               a 1...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

BIswajit_Das's avatar
BIswajit_Das
Impactful Individual
3 years ago

Actually i need the measure value in a column

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • BIswajit_Das's avatar
      BIswajit_Das
      Impactful Individual
      3 years ago

      Thanks it's really helpfull

    • BIswajit_Das's avatar
      BIswajit_Das
      Impactful Individual
      3 years ago

      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??

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I liked this solution