Forum Discussion

moniqueg's avatar
moniqueg
Frequent Visitor
3 years ago
Solved

Creating a calculated table based off two related tables

Hello,   I am struggling to figure out how to create a calculated table based off two other related tables.   I have a date table for which i would like to use the month end date as a group and a...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

    New Table = 
ADDCOLUMNS (
    DISTINCT ( 'Calendar'[End of Month] ),
    "@Received",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Received], 0 ) = 'Calendar'[End of Month] )
        ),
    "@Closed",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Closed], 0 ) = 'Calendar'[End of Month] )
        ),
    "@Assigned",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Assigned], 0 ) = 'Calendar'[End of Month] )
        )
)

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

 

 

New Table = 
ADDCOLUMNS (
    DISTINCT ( 'Calendar'[End of Month] ),
    "@Received",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Received], 0 ) = 'Calendar'[End of Month] )
        ),
    "@Closed",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Closed], 0 ) = 'Calendar'[End of Month] )
        ),
    "@Assigned",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Assigned], 0 ) = 'Calendar'[End of Month] )
        )
)