Forum Discussion

Migsmix's avatar
Migsmix
Frequent Visitor
5 years ago
Solved

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:   The column "N° of products bought" is a dax formula and PBI doesn't let me put it in a row inside the m...
  • MisterFry's avatar
    5 years ago

    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.