Starting out in Power BI, Need DAX help

[Fernus]

Hi,

I'm new to Power BI and almost pulling my hair out as I come across multiple DAX issues in trying to create new measures!

To start with can someone tell me why this formula will not work?  For a card visual, I'm trying to sum the lengths for unique project titles and where the parameter column is equal to 1 but for the love of God can't get it to work!

Total = calculate (sum(Sheet1[length]), distinct(Sheet1[project]), filter(Sheet1[parameter]=1))

So the following should equal 219 (12 + 7 + 200)

Length     Project        Parameter

12            A                 1

7              B                 1

200          C                 1

200          C                 1

200          C                 1

200          C                 1

50            D                 0

On a separate measure issue, I've learnt from watching tutorials where creating new measures is advocated instead of creating/adding new columns to your dataset to lend a less resource heavy file.  Again, I'm struggling badly trying to create the following if statement so I can extract figures from the fee column and force text values to be null.  When typing the formula I can only seem to select other measures previously created instead of being able to select the column I want. Does anyone know what I'm doing wrong?

Cost = IF(ISNUMBER(VALUE(Sheet1[fee])), Sheet1[fee]),"")

The fee column does have some cells with text values as mentioned like n/a.  Perhaps I am thinking in too much of in an Excel mindset but even when I try change the data type of the column from text to whole numbers I get the error "We can't automatically convert the column to Whole Number type." so creating a new column doesn't work either, is this because some cells contain text ie n/a?

Thanks in advance for your help!

[AlB]

Hi @Fernus 

It looks like you need to do some further reading to understand better basic concepts like row context, filter context, how CALCULATE works... Don't worry, it's normal at the beginning but those concepts are extremely important if you want to make any sense of DAX

For your first measure: 

Total = SUMX( FILTER( DISTINCT(Sheet1), Sheet1[Parameter] = 1), Sheet1[length] )

or another option:

Total V2 = SUMX( DISTINCT(Sheet1), IF( Sheet1[Parameter] = 1, Sheet1[length] ) )

As for the Cost measure, you are mixing several things there. It looks like you're actually looking for a calculated column, rather than a measure.  Something like:

Cost = IF( ISNUMBER( Sheet1[fee] ), Sheet1[fee]), "")

Row context is the important concept here. You cannot reference a column directly in a measure, since there is no row context there, only filter context. In any case, if Sheet1[fee] has "N/A" as some values then the whole column is of type text, so ISNUMBER( ) above will always return FALSE ( ). You'd probably another approach.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Fernus]

Hi AIB,

Thanks for the reply.  You are right, I probably do need a book or something as something is being lost in just relying on tutorials.

For the first measure I did try your suggested formula of (looks like you may have missed out [project] column ref in the distinct part?) 

Total = SUMX( FILTER( DISTINCT(Sheet1[project]), Sheet1[Parameter] = 1), Sheet1[length] )

 but this yielded the error 'A single value for column 'Parameter' in table 'Sheet1' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

Your alternative solution:

Total V2 = SUMX( DISTINCT(Sheet1[project]), IF( Sheet1[Parameter] = 1, Sheet1[length] ) )

Unfortunately, also generated a similar error but for 'length' in table 'Sheet1'?

As for the cost measure, the only other approach I can think of is going back into Excel and use a formula there in a new column to remove the text values if Power BI cannot be made to treat the odd text cells as null for a given column where I expect to otherwise treat as values.  Is this my best option?

[Nathaniel_C]

Hi @Fernus ,

Are you trying to create a measure or a calculated column?

Nathaniel

[AlB]

@Fernus 

Both measures are fine. The DISTINCT needs to be on the whole table, not one column, given what you want. See it all at work in the attached file.

As for the Costs column, I'd need to see the actual data to give you an accurate answer. If it's only numbers and N/A you could discard the NAs and convert the rest to number. Something like this should work:

Cost = IF( Sheet1[fee] <> "N/A" , VALUE( Sheet1[fee] ) )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Fernus]

@AlB After getting rid of a column which had a load of errors in it a value started to appear on the card with your formula but it is summing all the values in that column up instead of summing it distinctly ie 819 instead of 219 like in yours.  Lord knows why it is working for you but not me? 

Could it be because there are other columns are in the dataset which I have not mentioned as they are not relevent to this query?  Your cost formula worked a treat however!

@Nathaniel_C whatever works but I understand creating measures instead of columns wherever it can be helped is a better approach

[AlB]

@Fernus 

If you can share the pbix I'll have a look. Anyway, yes, showing the full table(s) is generally a good idea

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers