Forum Discussion

sonuojha1's avatar
sonuojha1
Icon for Helper IV rankHelper IV
5 years ago

Create union report in Power BI

Hi All,

 

I have a requirement to create a union report just like OBIEE as below. For this, I have 4 measures. Type is hardcoded string.

below is a measure formula.

measure1 = CALCULATE(SUM('Fact - Pay Costing'[CREDIT_AMOUNT]),FILTER('Fact - Pay Costing','Fact - Pay Costing'[CLASSIFICATION_ID]=67),FILTER('Fact - Pay Costing','Fact - Pay Costing'[EFFECTIVE_DATE]<=MAX('Dim - Date'[Date])))

 similarly, i have other 3 measures.

 

Kindly Note, I don't want to merge/Union tables by using power query.

 

Expected

YearMonthTypeAmount
2018JanEarning100
2018FebDeduction58
2018JanExpense32
2018JanPay59
2018FebEarning89
2018FebExpense67
2018FebPay62

 

OBIEE Union report

 

1.  Can we achive it without creating any additional table? If yes, how?
2.  By using calculated table:- If we use calculated table and store data as Month level, then how would be proceed with Time filters like year/month from Time Dimension table. As it has month as lowest level and Time Dim has day as lowest.

3. Any alternate option?

 

Regards,

Sonu

 

 

Need Help

2 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    sonuojha1  Can we achive it without creating any additional table?

    can you try this and see if it works

     

    neeMeasure=[measure1]+[measure2]+[measure3]+[measure4]

     

    tried with some simple measures

     

    m1 = IF(MAX('Table'[index])=1,1000)
m2 = IF(MAX('Table'[index])=2,2000)
m3 = IF(MAX('Table'[index])=3,3000)

     

     

     

    • sonuojha1's avatar
      sonuojha1
      Icon for Helper IV rankHelper IV
      5 years ago

      Thanks for reply,

       

      However, we have Year/Month data for other measures as well, hence we can't simply add them.

       

      Regards,

      Sonu