Forum Discussion

YesP's avatar
YesP
Icon for Helper I rankHelper I
1 year ago
Solved

Create a Conditional Measure working with a Direct Query Semantic Model

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,

 

Need Help
  • YesP's avatar
    YesP
    1 year ago

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

     

    Thanks for trying.

10 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi YesP 

    Try this:

    VAR SecondLetter =
    MID ( TableName[ColumnName], 2, 1 )
RETURN
    SWITCH (
        SecondLetter,
        "A", "Administrative",
        "L", "Liability",
        "O", "Operations",
        "U", "Utilities"
    )
    • YesP's avatar
      YesP
      Icon for Helper I rankHelper I
      1 year ago

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

       

      Thanks for trying.

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        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's avatar
      YesP
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        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's avatar
      YesP
      Icon for Helper I rankHelper I
      1 year ago

      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"
    )

        

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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