Forum Discussion

Rajeeb701's avatar
Rajeeb701
New Member
2 years ago
Solved

Matrix table in Power BI

Hi ,

 

I have attached 02 screenshots .. one is rawdata, second one is output view.

In output page need to add two slicer (Report date 01 and report date 02) user can select those data according to selection date should be visible in table.. 

if let me know if any confusion ..

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Rajeeb701 

     

    You can try the following methods.

    The first step is also, to select the first four columns-Unpivot Other Columns.

    Dates can be made into slicers.

    Measure = 
Var _mindate=CALCULATE(MIN('Table'[Report date]),ALLEXCEPT('Table','Table'[Report date])) 
Var _maxdate=CALCULATE(MAX('Table'[Report date]),ALLEXCEPT('Table','Table'[Report date]))    
Var _min=CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Head Property],'Table'[Property],'Table'[Demise Reference],'Table'[Attribute]),[Report date]=_mindate))
Var _max=CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Head Property],'Table'[Property],'Table'[Demise Reference],'Table'[Attribute]),[Report date]=_maxdate))
Return
IF(_min=_max,TRUE(),FALSE())
    Result = IF(HASONEVALUE('Table'[Report date]),SELECTEDVALUE('Table'[Value]),[Measure])

    Result:

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Rajeeb701 

     

    You can try the following methods.

    The first step is also, to select the first four columns-Unpivot Other Columns.

    Dates can be made into slicers.

    Measure = 
Var _mindate=CALCULATE(MIN('Table'[Report date]),ALLEXCEPT('Table','Table'[Report date])) 
Var _maxdate=CALCULATE(MAX('Table'[Report date]),ALLEXCEPT('Table','Table'[Report date]))    
Var _min=CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Head Property],'Table'[Property],'Table'[Demise Reference],'Table'[Attribute]),[Report date]=_mindate))
Var _max=CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Head Property],'Table'[Property],'Table'[Demise Reference],'Table'[Attribute]),[Report date]=_maxdate))
Return
IF(_min=_max,TRUE(),FALSE())
    Result = IF(HASONEVALUE('Table'[Report date]),SELECTEDVALUE('Table'[Value]),[Measure])

    Result:

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • Rajeeb701's avatar
      Rajeeb701
      New Member
      2 years ago

      Hi,

      I need a view as similar as output screenshot which I attached .

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    In Power Query, right click on the first 4 columns and select "Unpivot Other Columns".  Create a Calendar Table and build a relationship from the Date column of the Fact table to the Calendar Table.  Drag and drop date from the Calendar Table to the Column well of a matrix visual.  Create a Date slicer from the Calendar table and select 2 dates.  Write this measure

    Measure = calculate(max(Data[Value]),datesbetween(calendar[date],max(calendar[date]),max(calendar[date])))=calculate(max(Data[Value]),datesbetween(calendar[date],min(calendar[date]),min(calendar[date])))

    Hope this helps.