Create a Conditional Measure working with a Direct Query Semantic Model

[YesP]

Hi,

I am working with a Semantic Model so I am not able to use the Transform Data option from Power BI.

In the Semantic Model I have a column Code and I want to create a column Code Desccription. This new column is based in the second letter of the Code: For instance, if Code is CAB, second letter is A then Code Description will be Administrative. I want to use this Code Description in a Filter to create visualization with this Code Description column. Please your help!

Thanks,

[v-yangliu-msft]

Thanks for the reply from danextian , please allow me to add some more information:
Hi  @YesP ,

Here are the steps you can follow：

1. In Power Query – Add Column – Custom Column.

if Text.Start([Code], 1)="C" and Text.Length([Code])=3
then 1 else 0

if [Custom]=1 and Text.Middle([Code], 1, 1)="A" then "Administrative"
else
if [Custom]=1 and Text.Middle([Code], 1, 1)="L" then "Liability"
else
if [Custom]=1 and Text.Middle([Code], 1, 1)="O" then "Operations"
else
if [Custom]=1 and Text.Middle([Code], 1, 1)="U" then "Utilities"
else null

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[danextian]

Hi @YesP 

Try this:

VAR SecondLetter =
    MID ( TableName[ColumnName], 2, 1 )
RETURN
    SWITCH (
        SecondLetter,
        "A", "Administrative",
        "L", "Liability",
        "O", "Operations",
        "U", "Utilities"
    )

[YesP]

Hi @danextian , How I can add to this formule the following syntax:

If the column Code start with C and lenght=3 then secondLetter= "A","Administrative",.... the rest of the formule lines below. 

VAR SecondLetter =
    MID ( TableName[ColumnName], 2, 1 )
RETURN
    SWITCH (
        SecondLetter,
        "A", "Administrative",
        "L", "Liability",
        "O", "Operations",
        "U", "Utilities"
    )

[YesP]

@danextian It works! How I can use it in a filter? I try to use this new measure Description in a filter but it does not work. It just work in a table as long as I use the Code column as well.

I want the User be able to select the description in a filter and see all their transactions related to the description selected.

Please your help.

Thanks,

[danextian]

Create a calculated column instead. Measures don't have row context and are evaluated based on the other dimensions added to a viz so it cannot be used as a dimension or in a slicer. The first formula without SELECTEDVALUE creates a calculated column.

[YesP]

@danextian Thank you but I tried the first formule without SelectedValue but it does not works because my Datasource is a DirectQuery Semantic model storage in a different software.I think that I can not create a calculate column in Power BI using a DirectQuery Semantic model as Data source. Please any other sugestion how I can add the Description Column in a filter?

[danextian]

If it doesn't work because of Direct Query limitation, you can try creating a column in the query editor using first by adding a column that extracts the second letter and another conditional column to return the description. I've tested the steps below using MS SQL and they didn't break query folding. If this doesn't work, I would suggest to import the code column instead of DQ or create a view in SQL instead.

[YesP · Accepted solution]

@danextian it is not allowing me the pull the Table column,

Thanks for trying.

[danextian]

Hi @YesP 

It appears you are creating a measure and not a calculated column. You cannot directly access a column in a measure (unless in a table expression or when filtering) without aggregating it beforehand. Use this:

 MID ( SELECTEDVALUE ( TableName[ColumnName] ), 2, 1 )

[YesP]

Hi @danextian , This formule works great! you are awesome!  I want to add a condition, if a Code start with C and lenght =3 then 

VAR SecondLetter =

    MID(SELECTEDVALUE('Code'[Code ID]), 2, 1)

RETURN

    SWITCH (

      SecondLetter,

      "A","Administrative",

      "L", "Liabilities"

* Also, could you include Else (when the Code field is "blank","Unidentified"

Thank you so much,