Forum Discussion

Mahadi's avatar
Mahadi
Frequent Visitor
4 years ago
Solved

Value count problem

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 

 

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    @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 ğŸ™‚

7 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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. 

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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's avatar
      TheoC
      Community Champion
      4 years ago

      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 🙂

       

  • Mahadi's avatar
    Mahadi
    Frequent Visitor
    4 years ago

     

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

    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    @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 ğŸ™‚