Forum Discussion

Shamatix's avatar
Shamatix
Post Partisan
8 years ago

Question regarding measure

 Hey fellow users of PowerBI,

 

I have a question regarding a measure and something I want to achieve.


I have 3 tables


Table A

Column (Unique): Name

 

Table B (Main table)

Ton of data 

 

Table C (History table)

Has a message column saying: ColumnXTableB has changed from '' to 'abc' 

ColumnBTableB has changed from value 'asd' to 'sad'

 

All the 3 tables are linked together, now I want to create a table where first row is using Name from table A which is unique. 
Table B has a column with names from Table A for each row. I want to create a table which has most of the rows from Table B which has a percentage in each row / column for each name in table based on how often something has changed in table C to how many rows there are total.

 

Forexample if we take a look at the below picture and look at "Henry", lets say there is a total of 10 lines having the value (name) Henry in Table B, then I can see in Table C in column "Message" that "ValueBTableA has changed from 'abc' to '123'

 

So it has changed 1 out of 10 times (10%) so 90% of the time the value was correct and therefor wasnt changed,

one more example could be Henry Value A, here Message in Table C would say "that ValueATableA has changed from '' to '1253' and that would have happened 6 times, (60%) so only 40 of the time the value wasnt changed and therefor correct)

 

I hope you understand what I want to achieve, but I aint sure how to.

Hopefully some of you guys can help me out:) 

 

General Comment
Need Help
Show and Tell

17 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI Shamatix

     

    Any chance you can post some sample data?  Your explanation is good but it might help to provide a sample of data to work with.

    • Shamatix's avatar
      Shamatix
      Post Partisan
      8 years ago
      Phil_Seamark wrote:

      HI Shamatix

       

      Any chance you can post some sample data?  Your explanation is good but it might help to provide a sample of data to work with.

       

      Hey,


      I have fast made a power bi report here https://uploadfiles.io/n80cv  (Unsure where I could upload it)

      Anyways there is the 3 tables and a fast relationship table i need, well I have way more data but this is kind of the structure I have and I hope you get the larger idea of it all once you see those tables.

       

      Feel free to ask me any questions needed be and Ill do my best to answer, you can also feel free to add me on skype: Snaske2 if it might be easier for you.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi Shamatix

         

        I got your sample model and here is what I've got so far.

         

        I added a calcuated column to your TableC using the following code :

         

        Column Name = 
        var ConvertColonToSpace = SUBSTITUTE('Transfer (TableC)'[Message],":"," ")
        var FirstSpace = FIND(" ",ConvertColonToSpace,1,0)
        return LEFT(ConvertColonToSpace,FirstSpace)

        This gave me a field that I could drag to the Column of the matrix visual

         

        I then created the following calculated measure

         

        Count of rows in Table B = CALCULATE(
				COUNTROWS('Data (TableB)') , 
				ALLEXCEPT('Cred (TableA)','Cred (TableA)'[Name])
				)

        Which will give me the overall total of rows in TableB for each Name - which I will use in my final calculated measure :

         

        Measure = DIVIDE(
		COUNTROWS('Transfer (TableC)'),
		'Data (TableB)'[Count of rows in Table B]
		)

        If you create that measure (use a better name) on the model you uploaded, and format as percentage, I got the following result