Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
edatero
Frequent Visitor

matrix

Hello,

 

I am creating a Matrix / Pivot Table on Power BI. My 2 values per column id are both Distinct Counts.

 

Is there a way I can combine the columns, so that IF the PROD1 column is empty and ASSOC1 column is not, it returns a 0, or else it shows the PROD1 number?

 

Example: Product #11 would show a 0 where ASSOC1 is 1.

 

bi1.JPG

 

I want this outcome: where there are 0's, blanks, AND positive integers.

power2.JPG

 

I have excel experience, but can't seem to figure out syntax here. Also, is there a way to only have one column, with the calculation stated above? 3 Columns seem cluttered.

 

Thank you for your time!

8 REPLIES 8
shebr
Resolver III
Resolver III

Hi @edatero

 

Have you tried creating a column with the formula ="IF(IsBlank(PROD1) & NOT(ISBLANK(ASSOC1), 0, PROD1)"?

 

shebr

edatero
Frequent Visitor

Hello, Thank you for the suggestion!

 

Unfortunately, Yes, I have tried that and several variations. I have also tried attaching Count() and DistinctCount() for the measures.

 

Is it possible to create a formula that accounts for matrix Values, not just the columns and rows?

Perhaps a FOR loop for each Value that appears in the matrix?

Hi @edatero,

 

Maybe you could ceate measures to get the distinct count of PROD1 and ASSOC1. Then, create a third measure to combine their values like:

Measure=IF([MeasurePROD1]=BLANK() & [MeasureASSOC1]<>BLANK(), 0, [MeasurePROD1])

 

For more advice, please show us your desired output. Based on current description, I was confused about what you were trying to achieve.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @edatero

 

I dont believe so no, but im also struggling to understand exactly what it is you are trying to achieve. It sounds like its possible but its not the solution you want. Can you perhaps go through this again step by step? Or perhaps provide the pbix file or analysis?

 

Thanks

 

shebr

Anonymous
Not applicable

Can you post some example source data that you are trying to transofrm in the end result

Thank you for replying! Sure thing,

 

The first table is where id represents Products, and each Manufacturer of the specific product has its own numeric id.

 

The PROD1 Value in my matrix is the Distinct Count of how many Products each Manufacturer produces. (We can see that Manufacturer 1093 produces several items on this chart).

 

m_Product-Manu.JPG

 

This second table shows the relationship between Manufacturers and their respective Dealers/sellers.

 

In my matrix, the columns are Dealers, the rows are Manufacturers, and the Values are Count of Products (PROD1) and if a relationship exists between Dealers and Manufacturers (ASSOC1). I want a 0 to show if this relationship exists, but the Dealer has not received any of the manufacturer's items.

 

m_Manu-Dealer.JPG

 

Anonymous
Not applicable

What are you using to calculate ASSOC1?

 

Can you please post it so that I can work of of that

Yes! In the second table I posted, Dealers and Manufacturers are associated to each other by their arbitrary I.D. "key." If a key exists, it simply shows as "1" in the ASSOC1 column (The calculation is Distinct Count of these keys for each respective column-row).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.