topic Re: power bi loop in Developer

power bi loop

BIswajit_Das — Mon, 27 Feb 2023 11:39:30 GMT

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

Re: power bi loop

Anonymous — Wed, 01 Mar 2023 02:46:04 GMT

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

Re: power bi loop

BIswajit_Das — Wed, 01 Mar 2023 12:27:13 GMT

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

Re: power bi loop

BIswajit_Das — Wed, 01 Mar 2023 12:32:54 GMT

Actually i need the measure value in a column

Re: power bi loop

Anonymous — Thu, 02 Mar 2023 06:31:42 GMT

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

Re: power bi loop

BIswajit_Das — Fri, 03 Mar 2023 05:20:21 GMT

Thanks it's really helpfull

Re: power bi loop

BIswajit_Das — Fri, 03 Mar 2023 05:33:03 GMT

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

Re: power bi loop

Anonymous — Fri, 03 Mar 2023 06:27:04 GMT

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