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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Migsmix
Frequent Visitor

DAX Formula placed in rows in a matrix

Hi :)! 

 

Is there a way to put a dax formula in a row? I have something like this:

 

Migsmix_0-1616785355953.png

The column "N° of products bought" is a dax formula and PBI doesn't let me put it in a row inside the matrix. It only allows me to put it in values.

 

Thanks!!

1 ACCEPTED SOLUTION
MisterFry
Resolver III
Resolver III

This requires a bit of a more advanced solution. 

 

You're correct that you can not put a measure in rows. 

 

What you're trying to create here is essentially a dynamic histogram. At least, the theory is the same, even if it's not a proper histogram. 

 

Have a look at this: 

https://community.powerbi.com/t5/Desktop/Dynamic-Histogram-with-Measures/td-p/856240

 

If you have a discrete set of potential values, this becomes somewhat easier, also, if you're counting things, and counting by 1s then it's also a bit easier. 

 

Step 1) 

Create a table that contains your discrete values 

There are a few ways you can do this. You can either calculate the table in DAX, or you can load a table of your expected 'bucket' values, or use enter data to create your list of possible values. Pick your poison. 

 

Step 2) 

Create a dax formula that counts the number of things, in your case stores that have made 'n' purchases, where 'n' is the content of the 'bucket' field on the table that contains your discrete values.  

 

Then in your matrix, you use your bucket value field from the table you created, and for measures, you add the measure that does the counting, and it should create the visual you want. 

View solution in original post

8 REPLIES 8
MisterFry
Resolver III
Resolver III

This requires a bit of a more advanced solution. 

 

You're correct that you can not put a measure in rows. 

 

What you're trying to create here is essentially a dynamic histogram. At least, the theory is the same, even if it's not a proper histogram. 

 

Have a look at this: 

https://community.powerbi.com/t5/Desktop/Dynamic-Histogram-with-Measures/td-p/856240

 

If you have a discrete set of potential values, this becomes somewhat easier, also, if you're counting things, and counting by 1s then it's also a bit easier. 

 

Step 1) 

Create a table that contains your discrete values 

There are a few ways you can do this. You can either calculate the table in DAX, or you can load a table of your expected 'bucket' values, or use enter data to create your list of possible values. Pick your poison. 

 

Step 2) 

Create a dax formula that counts the number of things, in your case stores that have made 'n' purchases, where 'n' is the content of the 'bucket' field on the table that contains your discrete values.  

 

Then in your matrix, you use your bucket value field from the table you created, and for measures, you add the measure that does the counting, and it should create the visual you want. 

selimovd
Super User
Super User

Hey @Migsmix ,

 

so in general there are 2 concepts in Power BI.

You can have a calculated column, this new column is calculated when the file is loaded. Then the value is fix and it's another column that you can use.

The second one in a measure. The measure is always calculated during runtime and needs a context. So if you put the stores in a table the fomula "SUM(Sales)" will give you the number per store. If you remove it you will get the total, if you put in the date column it will calculate the sales by date.

 

So back to your question, do you want a new column with fix values or do you want to calculate it dynamically?

And then the second question why doesn't Power BI let you put it in a matrix? Do you get an error message? A little more information would help here.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @selimovd ,

 

U can see this example:

Migsmix_0-1616786187725.png

 

"Count" is a formula and it doesn't let me put it in the rows

 

Hello @Migsmix ,

 

yes because measures cannot be used as rows, as they need rows as a context.

Why do you want to put them as rows? Don't you want the count by "TIPO"?

 

Or how should the result look like?

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

Hi @selimovd ,

 

I have a lot of stores (Store ID) and i would like to know how many of them bought only 1 time, 2 times and so on. Maybe what i should do is a dax formula? Hope u can help me. I'm attaching my pbi file here PBI EXAMPLE  🙂 

 

Thanks!

Hi,

There will be many individual number in the row labels then.  If you want to bucket them into buckets such as 1-5 stores, 6-10 stores, then we can solve this problem.  Share your PBI Desktop file and also share the buckets that you want to see in the row labels.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
aj1973
Community Champion
Community Champion

Hi @Migsmix 

Is this what you want to see ?

aj1973_0-1616789658848.png

 

aj1973_1-1616789712341.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hey @Migsmix ,

 

I still didn't full understand how the result should look like. But the following measure should give you the desired result:

2 Purchases = IF( SUM( Tabla2[Purchases] ) = 2, SUM( Tabla2[Purchases] ) )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors