Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sql query to dax equivalent

Hello, I've been trying to solve an issue with my current dashboard and can't seem to find a solution. I have the right result when i check it on the database side with sql, but I just seem to be u...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    For someone looking for a possible solution to their problem, i eventually found it.

    similar to what amitchandak did but with a little tweak.

    countx(
      filter(
        values(dbw_inv_projecto[nome]),
        calculate(
          distinctcount(dbw_inv_proj_etapa[dimensao])
        )> 1
      ),
      calculate(distinctcount(dbw_inv_proj_etapa[dimensao]))
    )
     
    the end result can be presented in a card. but if you're looking for input for
    for a table, use amitchandak 's solution.
    hope this helps someone.
amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try a measure like

 


Measure =
var _1 = calculate(distinctcount(proj_etapa[dimensao]))
return
sumx(filter(values(projecto[nome]), > _1), _1)

 

Both table should be joined in power bi

 

you can use nome in visual

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak, thanks for the reply
    but i think there is something missing from your solution.

     

    when you use  "filter(values(projecto[nome]), > _1)"

    i assume you meant something like: filter(values(projecto[nome]), _1> 1).

    if my assumption is right, it still gives me the wrong result.

     

    it is giving me the total sum of "dimensao" for all projects instead of giving me the intended result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      For someone looking for a possible solution to their problem, i eventually found it.

      similar to what amitchandak did but with a little tweak.

      countx(
        filter(
          values(dbw_inv_projecto[nome]),
          calculate(
            distinctcount(dbw_inv_proj_etapa[dimensao])
          )> 1
        ),
        calculate(distinctcount(dbw_inv_proj_etapa[dimensao]))
      )
       
      the end result can be presented in a card. but if you're looking for input for
      for a table, use amitchandak 's solution.
      hope this helps someone.