Value count problem

[Mahadi]

Hello good people, I am a new Power BI learner and have encountered a problem while designing a data model. I have attached the file where I am working on ContosoDW. You can see the values are the same. I need your experienced opinion. I will be very greatful if you are able to point out the exact problem in the table connection. Thank you.   https://drive.google.com/file/d/1U-O8ctnBOkIb8E3-16IJ_uQQF8wAX3SB/view?usp=sharing 

[TheoC · Accepted solution]

@Mahadi okay, all you need to do is add a new column in the DimProduct table like below and then use the YEAR from the DimProduct table instead of the Date table.  Keep the Count of ProductName the same.

Hope this helps!

Theo 🙂

[Ashish_Mathur]

Hi,

What are you trying to accomplish by dragging Product Name to your visual?  If you wan to know how many products are available for sale, then create a relationship from the AvailableForSaleDate column of the Products Table to the Date column of the DimDate Table - you will have to modify other relationships before you create this new relationship.

So please clarify what exactly do you want to accomplish.

[HotChilli]

Hi @Mahadi @TheoC ,

I hope I'm not stepping on any toes but I thought I'd respond since Theo is answering from his phone.

The visual created has 2 fields from dimension tables (which is unusual) and if you look at the data model and  the relationship directions, they are 1:many from the dimension tables to the FactSales table.

i.e. date table ->FactSales <- product table so the date table doesn't know anything about the product table unless something from the FactSales table is involved.  A typical symptom of this sort of thing is when all the aggregation values for different dimension values are the same.

Dimension tables are used to slice and dice data from a fact table so in this instance you will need to decide what you actually want in the visual, for example, Calendar Year from the date table and count/distinct count of product ID from the FactSales table.

---

There's quite a lot to grasp here so I hope it makes sense.

Good luck in your powerbi journey. 

[Mahadi]

 

Hi @TheoC , it didn't work. I cannot figure out what's the problem actually. 

[TheoC]

Hi @Mahadi,

Certainly take a look at what @HotChilli has provided. It makes a lot of sense. I'd recommend following their approach to correcting the data model.

If you need more information on what @HotChilli has provided, make sure to comment and ask them. The Super Users and Community members are pretty amazing people!

All the best and look forward to you getting a solution for your current issue.

@HotChilli Thanks heaps for helping out!

Cheers guys!

Theo

[TheoC]

Hi @Mahadi 

Can you use this measure and let me know if it works:

Measure = COUNTROWS ( Table )

Then drag it to the table visual? Apologies, on phone so can't download file.

hope this helps.

Theo

[TheoC]

@Mahadi okay, all you need to do is add a new column in the DimProduct table like below and then use the YEAR from the DimProduct table instead of the Date table.  Keep the Count of ProductName the same.

Hope this helps!

Theo 🙂