Pivot (Matrix) and Visual based on two Bins/Bucket columns

[androo]

I am working in Power BI and am trying to solve a problem and create a visualisation that is pretty trivial in Excel, but, I can't figure out how to do it in PBi. I've uploaded an XL file which shows what I want to do, but, in Power BI.

The data table in the my real problem is pulled in by Power Query, and Vals 1 and 2 are calculated columns in PBi based on other cols pulled in by PQ (not shown in my XL example, I don't think it matters that I'm not showing the cols that Val 1 and Val 2 are calculated from). Bins 1 and 2 in turn are columns created by PBi's "Data Groups" feature and based on the calculated columns Val 1 and Val 2.

The end result required is a table and a chart showing the count of values falling in Bins 1 and 2 against one another. Like the "Bins Table" and "Chart One" in my XL workbook. 

Of course my real problem has a lot more data, but, I think this captures what the problem is. I've tried a few things, but, for example, as you can only use one relationship between tables in PBi what I get using that approach is the result shown in chart 2 of the XL workbook. I thought of doing something analagous to the "Sumif" approach that works in Excel (albeit, an aside, that structured references don't seem to work in Excel with the sumif function), but, that would require two relationships between my "Bins Table" and my "Data table" and PBI won't allow that. Is there a way to do this with measures? With a "detached table". I'm stumped at the moment.

Here's a link to my XL workbook Chart from Two Bins

[v-rzhou-msft · Accepted solution]

Hi @androo ,

Here I create a sample by data in Excel to have a test. 

Here I inactive both relationships from [Bin Tag] to [Bin1] and [Bin2].

Measure:

Bin1 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin1],Bin[Bin Tag]),'Table'[Bin1]<>BLANK())

Bin2 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin2],Bin[Bin Tag]),'Table'[Bin2]<>BLANK())

Bin2 count based on Bin1 = CALCULATE([Bin1 count],'Table'[Bin2]<>BLANK())

Result is as below.

Best Regards,
Rico Zhou

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

[androo]

Oh, I did come across a Row Level Security problem in the past where I wanted to use "userelationship" but, apparently, in that scenario you can't. So that somwhat put that option out of my mind. But, it seems that it is a way to have two relationships, I'm presuming that at least one of them has to marked as inactive (although both are here) at least for limited purposes, from one table to another, that becomes a use case for the "use relationship" function.

[androo · Accepted solution]

hi Rico,

I thought the answer would be measures, and, thanks, yes it seems it is. A bit of a pain as it means I have to write two long measures creating the ten or so bins I need rather than using the PBi built in grouping directly, but, it works and means I can then use slicers from my category data with the visual and get sense. So, thanks. I've uploaded a version of my example file, in PBi fixed and with some superfluous elements removed so that the solution is as clear as possible.

[androo]

I have been able to partially solve my problem. I created a BINs table in Power BI and then added two columns each counting the occurences of the Bin ID fromthe Bin Table value in the Bin 1 and then Bin 2 column of the data  table.

Curr Bin Count = calculate(DISTINCTCOUNT(EST077_Full_List[Property Reference]),filter(EST077_Full_List,EST077_Full_List[Bins: Curr PW]=Bins[Bin]))

Prop Bin Count = calculate(DISTINCTCOUNT(EST077_Full_List[Property Reference]),filter(EST077_Full_List,EST077_Full_List[Bins: Proposed PW]=Bins[Bin]))

For the limited aim of creating the "BIN table" and the "chart one" this has worked. However, it isn't a great result as I can't put this on another visual and then slice and fillter by other criteria in my model, which is why I think there is probably a better approach using measures.

[v-rzhou-msft · Accepted solution]

Hi @androo ,

Here I create a sample by data in Excel to have a test. 

Here I inactive both relationships from [Bin Tag] to [Bin1] and [Bin2].

Measure:

Bin1 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin1],Bin[Bin Tag]),'Table'[Bin1]<>BLANK())

Bin2 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin2],Bin[Bin Tag]),'Table'[Bin2]<>BLANK())

Bin2 count based on Bin1 = CALCULATE([Bin1 count],'Table'[Bin2]<>BLANK())

Result is as below.

Best Regards,
Rico Zhou

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