Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power BI Dynamic Grouping

I've a requirement on a Table to summarise the data based on below scenarios: The Table 1 is the existing, the Table 2 is what is expecting. Table 1 Item# ItemName Reason Catogery LostSales ...
DAX. CALCULATE
  • saykasku's avatar
    saykasku
    7 years ago

    Hi,

     

    You need to create measure like this:

     
    Reason_new = 
var _Reason = LASTNONBLANK(Arkusz1[Reason];1)
RETURN
IF(
    CALCULATE( DISTINCTCOUNT(Arkusz1[Reason]) ;
    all(Arkusz1[Reason])) > 1
    ;"Mixed"
    ;_Reason
    )

    This is solution for you.

     

     

     
saykasku's avatar
saykasku
Resolver I
7 years ago

Hi,

 

You need to create measure like this:

 
Reason_new = 
var _Reason = LASTNONBLANK(Arkusz1[Reason];1)
RETURN
IF(
    CALCULATE( DISTINCTCOUNT(Arkusz1[Reason]) ;
    all(Arkusz1[Reason])) > 1
    ;"Mixed"
    ;_Reason
    )

This is solution for you.

 

 

 
  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    I'm not sure what behaviour you want when filters are applied via slicers that result in only 1 reason being present, but just note that saykasku 's approach will always return "Mixed" regardless of any current filters, while the approach using SELECTEDVALUE() function will switch to showing the single reason if the current filter context eliminates the multiple reasons. Both approaches are valid, it just depends on what your requirements are.

     

    eg

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Is there some way to have something like this that will instead solo out Columns along with their supporting rows? I also need a way to group 4 columns without changing the data just want power BI to recognize that these 4 columns are all under 1 main name