Create new calculated table with multiple calculated column from 2 different tables with DAX

[armeizal]

Hello,

I am new to this. I need help to add new calculated table with multiple calculated column with DAX.

I have 2 table as below:

Table A:

Date A	List A
1/07/2020	Content A1
1/07/2020	Content A2
1/07/2020	Content A3
12/07/2020	Content A4
12/07/2020	Content A5
12/07/2020	Content A6
12/07/2020	Content A7
12/07/2020	Content A8
12/07/2020	Content A9

Table B:

Date B	List B
1/07/2020	Content B1
1/07/2020	Content B2
1/07/2020	Content B3
1/07/2020	Content B4
1/07/2020	Content B5
12/07/2020	Content B6
12/07/2020	Content B7

I would like to count the content in table A and table B based on date as follow:

Date	List A Count	List B Count
1/07/2020	3	5
12/07/2020	6	2

How to do that?
Thank you.

[amitchandak]

@armeizal , Create a common date table and use countrows from each table .

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[harshnathani · Accepted solution]

Hi @armeizal ,

Make sure you have a date calendar and it has been marked as the date in model view.
Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Create 2 measurse

COUNTTableA = COUNT('TableA'[ListA])

CountTableB = COUNT('TableB[ListB])

Regards,

Harsh Nathani

[armeizal]

Hello @harshnathani 

It works for me, but there are blank space between 24-Jul to 30-Jul, but it wouldn't be a problem for my needs when I use this table to line chart.

Thank you.

[harshnathani]

Hi @armeizal ,

Thanks . If this works kindly mark this as a Solution so that it helps other community members.

Cheers,

Harsh Nathani