Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Counting the records :-(

  • 1.  Counting the records :-(

    Silver Contributor
    Posted Jul 10, 2019 07:38 AM
    Hello, help....
    What seemed to me simple becomes difficult ... at the base I have a table with 2 columns. Each column contains 'services'. Count the number of service this way see my screenshot and my source table.
    When I create my second measure for my second service here is the result that is wrong. I should have a 1 for 'sias' and a 1 for 'corali'
    I made several attempts with 2 tables and created a relationship. When I use the 'relatedtable' function it works but only for the first column. I also created a 'many to one' relation to use the RELATED (column) function which seemed to me more suitable but also no problem for the first column but the second one gives me a false result. I do not use the right solution and I do not know how to find the solution. Someone would have the good idea. Thanking you in advance. :-)


    ------------------------------
    pascal millet
    0493955869
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Counting the records :-(

    Top Contributor
    Posted Jul 10, 2019 08:38 AM

    Hi @pascal millet:

    Try

    MeasureA:= Countrows(Table[Column1])

    MeasureB:= Countrow(Table[Column2])

    Hope this helps,
    William​



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Counting the records :-(

    Silver Contributor
    Posted Jul 10, 2019 09:26 AM
    Hi william,
    Thank you for your help but this function does not take into account the columns but only the name of the table.
    the order does not work at home :-(


    ------------------------------
    pascal millet
    0493955869
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Counting the records :-(

    Posted Jul 10, 2019 09:29 AM
    Can you reply with the .PBIX file please

    ------------------------------
    Ethan Owens
    Centerville
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Counting the records :-(

    Silver Contributor
    Posted Jul 10, 2019 10:31 AM
      |   view attached
    Hi,
    It sounds simple but not so simple. Thank you


    ------------------------------
    pascal millet
    0493955869
    ------------------------------

    Attachment(s)

    pbix
    DSI_14new.pbix   241K 1 version
    Academy - Online Interactive Learning from Experts


  • 6.  RE: Counting the records :-(

    Top Contributor
    Posted Jul 10, 2019 09:50 AM
      |   view attached

    Hi @pascal millet:

    You are correct - a seemingly simply question turns rather complex!

    'More or less' you need to filter out the blanks from [Service1]; just seems more complex than it should be...


    Hope this helps!
    William



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------

    Attachment(s)

    pbix
    Millet_Help.pbix   20K 1 version
    Academy - Online Interactive Learning from Experts


  • 7.  RE: Counting the records :-(

    Silver Contributor
    Posted Jul 10, 2019 10:35 AM
    Thanks again. it reassures me because it's been several days that I tried to find. :-) It may be necessary to chained functions but it's been very short time that I work in power bi.


    ------------------------------
    pascal millet
    0493955869
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Counting the records :-(

    Silver Contributor
    Posted Jul 10, 2019 11:48 AM
    sorry williams,
    I just saw your file because when I'm at my company with the antivirus I do not always have access to the attached file. Arriving at home I connected and there with my personal connection I realized your research work. it is a command that I tested in all directions but I never thought that the whites posed a problem. I will be able to ride a bike without thinking about my problem. thank you again you are the strongest I give you the yellow jersey of the bike tour de France ......... to pleasure


    ------------------------------
    pascal millet
    0493955869
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Counting the records :-(

    Silver Contributor
    Posted Jul 10, 2019 12:23 PM
    William, I shouted victory too quickly because the measure B looks good but it does not position the elements in the right place. If you look at the table1 I should have 1 opposite SIAS and 1 opposite Corali but in the column service1 I have 1 opposite caf ...... in your opinion where is the problem?


    ------------------------------
    pascal millet
    0493955869
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Counting the records :-(

    Top Contributor
    Posted Jul 10, 2019 01:22 PM
      |   view attached

    Hi @pascal millet

    Hopefully I can get that (PBI) yellow shirt soon!


    The issue is that there is no logical relationship between [Service] & [Service1]

    Note, there are MANY ways to solve this - I used a data modeling scheme to correct.
    Here it goes:
    1) Create a new table that lists all potential values that you want to show (reference NEW_ValuesTable in my model)

    2) Create inactive relationships between the original table and the new table
    3) Create DAX measure that count the original table based on the relationship desired

    Other notes:
    a. you might notice that in Measure B is filtered the OriginalTable to show no blanks. I solved the issue through DAX, but there are other ways to tackle the issue.

    Again, hope this helps and happy biking!
    William


    #DAX #PowerBI #DataModeling
    ​​​

    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------

    Attachment(s)

    pbix
    Millet_Help.pbix   27K 1 version
    Academy - Online Interactive Learning from Experts


  • 11.  RE: Counting the records :-(

    Silver Contributor
    Posted Jul 11, 2019 04:34 AM
    Hello William, the sun has risen and my legs hurt ...... hi, hi, hi ... so I tested your solution. I added 22 services and everything works. Here is a photo of the yellow jersey at the moment, it's a french .... (Lance Armstrong won it 5 times  :-(, too strong.Without you I will not have found the solution. I did not know that we could make virtual connections. That will serve me.thanks a lot for your help.
    
    The yellow jersey is a distinctive jersey of yellow color worn by the rider occupying the first place of a ranking during some road cycling competitions, including the Tour de France.


    ------------------------------
    pascal millet
    0493955869
    ------------------------------

    Academy - Online Interactive Learning from Experts