Count distinct of a text column that changes according to the filter/rows

[jessicarocha]

I have a table in this structure:

Material ID		Fiscal Year
89809807		2017/18
56987098		2017/18
89809807		2017/18
34678907		2018/19
56987098		2018/19
56987098		2018/19
11111357		2018/19

I would like to create a card or a table that shows the total number of different products. It should be possible to filter per fiscal year. So, this is what I would like to have: 

Fiscal Year		Number of different materials
2017/18		2
2018/19		3

Instead, I am getting how many distinct materials there are in total, without considering the year at all. I get this: 

Fiscal Year		Number of different materials
2017/18		4
2018/19		4

I tried to create a quick measure. I tried to use the "count distinct" option of when you put a variable in the table. I also tried to calculate some measures but the result is always the same. 

Additional info: both columns are text data type

This is a fact table. So there is a relation of 1 to many with Date table, for example. 

Example of a created measure:

SUMX (

VALUES ( 'Dim Fiscal Year'[Fiscal Year]),

CALCULATE ( DISTINCTCOUNT ( 'Fact v06'[IDNR] ) )

)

Do you have any idea where my problem could be? 
Thanks in advance!

Dummy example file:
Dummy PowerBI  

I am unable to upload the file here. Therefore, it is in google drive.
The data source is a folder. Two excel files are combined.

[moizsherwani · Accepted solution]

See if the below helps

Relationship Deactivated (getting the same result as you)

Relationship Activated (please note you must use the Fiscal year from the Dim table and not the Fact table)

Also I used the same measure as you did

[jessicarocha]

Hi @moizsherwani . thanks for the help! 
i activated the relationship again, just like you did, to see if this was really the problem. but I still get the same weird result...

[moizsherwani]

1) Check to ensure the relationship between the date and fact table is on the correct field

2) Pull the date and ID from the Fact DNK6 Table into a visual and see what comes up (don't use the fiscal year table). If it still shows duplicates it might be that your Fact DNK6 Table has either some

duplicate data (or some erroneous data).

If that doesn't work I would also suggest sending the pbix with sample data for a view.

Regards,

Moiz

If this post helps, please Accept it as Solution to help other members find it.

[jessicarocha]

Hi @moizsherwani 

I still can't find the error. So I created a sample data and pbix to see if you can help me. 
Thank a lot for the effort, I really appreciate it!

Unfortunatelly I can not add the files here. It says that the option is not supported for me. Maybe because I am a new member?! So, I added in a google drive folder, I hope it is ok. Here is the link to access: 
Dummy PowerBI 

Info: The source of the data is a folder. The excel files are combined.

Kind regards, 

Jéssica

[moizsherwani]

I downloaded your dummy file. Just looking at it by eye in the data I see 6 distinct IDNR for each fiscal year (excluding duplicates) which is what is showing on the visuals. What is the count you are expecting?

[jessicarocha]

@moizsherwani you are totally right. I just realised now that I was missing one filter. I am so sorry for all the trouble. Thanks again for the help! It was very useful! 

[StefanoGrimaldi]

caus you have the relantionship inactive, isnt a active relantionship, looks like you have some issue on the relantionship of the model send a full pic of all the relationship, or send the pbix with dummy data

[jessicarocha]

hi @StefanoGrimaldi 

thanks for the explanation. I thought that was the same thing. I cleaned the model a bit and activated everything again. still have the same problem. Here are the pics as requested: 

[jessicarocha]

@StefanoGrimaldi 

I created a dummy example so you can understand better my problem. I really appreciate the effort in trying to help me. Thanks a lot, really!

I am unable to upload files here. So I created a folder in Google Drive. I hope it works. 

Dummy PowerBI 

PS: The data source is a folder. It combines the excel files.

[StefanoGrimaldi]

I would recommend you to post the pbix with dummy data, cause there could be a number of possible issue that its generating this problem, a quick look at the result vs how you say it should appears look like a relantionship problem between fact table and the date table, or the fields from each you using to arm the visual, you dont need dax also for this, you can do this with a simple table visual and setting de materiales to count distint on the field section of the visual,

again, share a dummy data pbix to get a pinpoint solution for your problem, or add screemshot of how you have everything set up 

[jessicarocha]

@StefanoGrimaldi I am so sorry for all the trouble. It was my fault. I was missing one filter. That is why the count was weird. Thanks again for the help!

[jessicarocha]

Hi @StefanoGrimaldi 

Thank you for answering me.
I don't think the problem is in the relationship. I deactivated everything and I am using the data of a single table. Still, the result is weird when I use the count distinct.