Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated table, count Values in table A and list them in table B

Hi! 
I'm trying to archive the below, but I have no idea what DAX to use. Is anyone able to help?

I am trying to calculate a table by counting how many entries have a certain attribute. 

This is what Table A looks like

 

Time ATime B
34
61
26
34

 

This is what the end result should look like

 

VTime ATime B
101
210
320
402
500
611

 

Thanks in advance,
JT

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    I Solved it!

    I created a new Table unsing excel with the numbers 1 to 24

    I then calculated new rows using:

    Time A = 
   IF(
    LOOKUPVALUE(TableA[TimeA], TableA[TimeA], TableB[V]) 
    =TableB[V], 
    CALCULATE(
        COUNT(TableA[TimeA]), 
        FILTER(TableA, TableA[TimeA] = TableB[V])
    ),
    0)

     

4 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    You can do this by creating a 'numbers' table which you could hardcode e.g. 1->6 or create dynamically.  I'll let you investigate how to do that.

    You could unpivot Table A and then create a relationship between the numbers table and the unpivoted Table A.

    Create a measure which will be a simple count of the Values .  If you put (+ 0) in the measure that will help later.

    Now create a matrix visual with the number from the numbers table, attribute from unpivotted Table A and the measure in the Values section.

    Well that's a description of what to do.

    Let me know how you get on.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      First: thanks for fast help.
      I guess I did not quite explain myself all to well. 
      The original Table A as over 15K entries. 
      I want to calculate Table B (the final result) to use it for a bar-graph. 

      Is the path described by you still viable? By hardcoding, do you mean using a stacked IF-Function? But how do assign the results to Table B? Sorry for asking such noob questions, but hours of youtube and searching the forum didnt produce any results. 

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    If the real data/model are close to the model provided it should work the same.  If they are materially different you'll have to repost with more information.

    I suggest you go through my suggested solution with the test data and try to make it work.  I won't do it for you (that's not my style) but I will help you if you put in the effort.

    1st step.  create a numbers table (search online for powerbi numbers table). It won't be with an IF statement.

    ---

    "But how do assign the results to Table B?" Table B is the matrix visual, so follow the instructions. I will help if you get stuck

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I Solved it!

    I created a new Table unsing excel with the numbers 1 to 24

    I then calculated new rows using:

    Time A = 
   IF(
    LOOKUPVALUE(TableA[TimeA], TableA[TimeA], TableB[V]) 
    =TableB[V], 
    CALCULATE(
        COUNT(TableA[TimeA]), 
        FILTER(TableA, TableA[TimeA] = TableB[V])
    ),
    0)