How can I display 0 count values on a table filtered with values from another table?

[MyWeeLola]

Good afternoon

I have 2 tables.

TblRef

A

B

C

D

and

tblmain

A

B

C

D

E

F

G

H

A

B

B

C

C

I want to dsiplay a table which filters tblMain to teh values in tblRef, and displays 0 values.

In the examples above I want the display table output to look as below.

Value	Count
A	2
B	3
C	3
D	0

How do I achieve this?

I cannot seem to get the filter correct and when I use a measure to count, I tried adding +0 to the end which then gave me the non tblref values.

I look forward to hearing from you.

Lola

[rajendraongole1 · Accepted solution]

Hi @MyWeeLola - if a value from tblRef does not exist in tblMain, it still shows a count of 0. but D is present in main table so we are gtting 1 . 

Count for tblRef =

VAR refValues = SELECTEDVALUE(Tblref[TblRef])

RETURN

    CALCULATE(

        COUNTROWS(tblMain),

        Tblmain[tblmain] = refValues

    )

Hope it helps

[uzuntasgokberk · Accepted solution]

Hello @MyWeeLola,

Make relationship your table one to many. İf TblRef have unique values.

After that You use below the dax code and for example my column name is "Text" but ı take the column from dimension table which means unique column table.

However, you can't use the masure in slicer. So that I recommend to use the measure as filter pane.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

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

[MyWeeLola]

@uzuntasgokberk 

Thank you for this, if worked perfectly and I am using this solution for the issue.

[rajendraongole1 · Accepted solution]

Hi @MyWeeLola - if a value from tblRef does not exist in tblMain, it still shows a count of 0. but D is present in main table so we are gtting 1 . 

Count for tblRef =

VAR refValues = SELECTEDVALUE(Tblref[TblRef])

RETURN

    CALCULATE(

        COUNTROWS(tblMain),

        Tblmain[tblmain] = refValues

    )

Hope it helps

[MyWeeLola]

@rajendraongole1 

Thank you for this, it worked as i needed it too, thank you.